package dao;

import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

import entity.customer;
import jdbc.JdbcDatabase;

public class CustomerImpl implements CustomerDAO {

	@Override
	public List<customer> findByuerid()throws SQLException {
        Connection conn = JdbcDatabase.getConnection();
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery("SELECT username, password, usersex, birthday, email FROM  dbo.CUSTOMER");

        List<customer> gs = new ArrayList<customer>();
        customer g = null;
        while(rs.next()){
            g = new customer();
            g.setUsername(rs.getString("username"));
            g.setPassword(rs.getString("password"));
            g.setUsersex(rs.getString("usersex"));
            g.setBirthday(rs.getString("birthday"));
            g.setEmail(rs.getString("email"));
            gs.add(g);
        }
        return gs;

	}

	@Override
	public void add(customer g) throws SQLException, ParseException {
		// TODO Auto-generated method stu
	    Connection conn = JdbcDatabase.getConnection();
        //sql

			Statement stmt=conn.createStatement();
			String sql="INSERT INTO dbo.CUSTOMER(username, password, usersex, birthday, email, phoneno, regdate, lockstate, lastaccess, login)"
	                +"values(?,?,?,?,?,?,?,?,?,?)";
	        PreparedStatement ptmt = conn.prepareStatement(sql); //预编译SQL，减少sql执行
	        ptmt.setString(1, g.getUsername());
	        ptmt.setString(2, g.getPassword());
	        ptmt.setString(3, g.getUsersex());
	        ptmt.setString(4, g.getBirthday());
	        ptmt.setString(5, g.getEmail());
	        ptmt.setString(6, g.getPhoneno());
	        ptmt.setString(7, g.getRegdate());
	        ptmt.setString(8, g.getLockstate());
	        ptmt.setString(9, g.getLastaccess());
	        ptmt.setInt(10, Integer.parseInt(g.getLogin()));

	        //执行
	        ptmt.execute();
				System.out.println("新加成功");
		}

	@Override
	public void update(customer g)throws SQLException {
		   //获取连接
        Connection conn = JdbcDatabase.getConnection();
        //sql, 每行加空格
        String sql = "UPDATE dbo.CUSTOMER" +
                " set birthday=?, lockstate=?, lastaccess=GETDATE(), login+=1 WHERE USERNAME = ?";
        //预编译
        PreparedStatement ptmt = conn.prepareStatement(sql); //预编译SQL，减少sql执行

        //传参
        ptmt.setString(1, g.getBirthday());
        ptmt.setString(2, g.getLockstate());
        ptmt.setString(3, g.getUsername());
        //执行
        ptmt.execute();

	}

	@Override
	public boolean delete(customer c) {
		// TODO Auto-generated method stub
		return false;
	}

}
